package com.inspect.dbConn;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.apache.log4j.Logger;

import com.inspect.log.LogSingleton;

/*	@project: auto inspect
 *  @author:	yangshengcheng@gzcss.net
 * 	@description: jdbc common operations package 
 * 	@create: 2012.08.21
 *  @version :20120821_alpha
 * 
 */

public class database {

	private static Logger log = LogSingleton.get_logger(database.class);
	private Connection db_connect = null;

	public database(String host, int port, String db_instance, String db_user,
			String db_passwd, String db_vender) {
		try {
			if (db_vender.equals("oracle")) {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				this.db_connect = DriverManager.getConnection(
						"jdbc:oracle:thin:@" + host + ":" + port + ":"
								+ db_instance, db_user, db_passwd);
			} else if (db_vender.equals("mssql")) {
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				this.db_connect = DriverManager.getConnection(
						"jdbc:sqlserver://" + host + ":" + port
								+ ";DatabaseName=" + db_instance, db_user,
						db_passwd);
			} else if (db_vender.equals("mysql")) {
				// reserve for other databases
			}

		} catch (ClassNotFoundException e) {
			log.error("连接数据库异常1",e);
		} catch (SQLException e) {
			log.error("连接数据库异常2",e);
		} catch (Exception e) {
			log.error("连接数据库异常3",e);
		}

	}

	/* return a global connect */
	public Connection get_db_conn() {
		return this.db_connect;
	}

	public boolean db_execute(String sql) {
		Statement stmt = null;
		boolean b = false;

		try {
			stmt = this.db_connect.createStatement();
			b = stmt.execute(sql);
		} catch (Exception e) {
			log.error("连接数据库异常",e);
		}

		return b;

	}

	/* update action, such as update insert delete */
	public int db_update(String sql) {
		Statement stmt = null;
		int b = 0;

		try {
			stmt = this.db_connect.createStatement();
			b = stmt.executeUpdate(sql);
		} catch (Exception e) {
			log.error("连接数据库异常",e);
		}

		return b;
	}

	public ArrayList<HashMap<String, String>> db_query_assoc(String sql) {
		ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();

		Statement stmt = null;
		ResultSet rs = null;

		ResultSetMetaData rsmd = null;

		if (this.db_connect == null) {
			return null;
		}

		try {
			stmt = this.db_connect.createStatement();
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int columns_num = rsmd.getColumnCount();
			while (rs.next()) {
				HashMap<String, String> hm = new HashMap<String, String>();
				for (int i = 0; i < columns_num; i++) {
					// System.out.println(rsmd.getColumnName(i+1)+ ' ' +
					// rs.getString(i+1));
					hm.put(rsmd.getColumnName(i + 1), rs.getString(i + 1));
				}

				al.add(hm);
			}

			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			log.error("连接数据库异常",e);
		}
		return al;
	}

	public ArrayList<ArrayList<String>> db_query_array(String sql) {
		ArrayList<ArrayList<String>> al = new ArrayList<ArrayList<String>>();

		Statement stmt = null;
		ResultSet rs = null;

		ResultSetMetaData rsmd = null;

		if (this.db_connect == null) {
			return null;
		}

		try {
			stmt = this.db_connect.createStatement();
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int columns_num = rsmd.getColumnCount();

			while (rs.next()) {
				ArrayList<String> al_temp = new ArrayList<String>();
				for (int i = 0; i < columns_num; i++) {
					al_temp.add(rs.getString(i));
				}

				al.add(al_temp);
			}

			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			log.error("连接数据库异常",e);
		}
		return al;
	}

}